package core;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import util.DB;

public class UserGroupDAO {

    private static UserGroup makeUserGroup(ResultSet rs, boolean count) throws Exception {
        UserGroup obj = new UserGroup();
        obj.setId(rs.getInt("Id"));
        obj.setName(rs.getString("name"));
        obj.setCategory(rs.getString("category"));
        obj.setCount(count ? rs.getInt("count") : 0);
        return obj;
    }

    public static UserGroup selectById(int id) throws Exception {
        String sql = "SELECT * FROM [UserGroup] WHERE [id] = ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? makeUserGroup(rs, false) : null;
            }
        }
    }

    public static UserGroup selectByName(String name) throws Exception {
        String sql = "SELECT * FROM [UserGroup] WHERE [name] = ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setString(1, name);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? makeUserGroup(rs, false) : null;
            }
        }
    }

    public static ArrayList<Integer> selectByUserId(int userId) throws Exception {
        String sql = "SELECT [userGroupId] FROM [User_UserGroup] WHERE [userId] = ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                ArrayList<Integer> list = new ArrayList<Integer>();
                while (rs.next()) list.add(rs.getInt("userGroupId"));
                return list;
            }
        }
    }

    public static UserGroup selectByUserIdAndGroupName(int userId, String userGroupName) throws Exception {
        String sql = "EXEC userGroupSelectByUserIdAndGroupName ?, ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setString(2, userGroupName);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? makeUserGroup(rs, false) : null;
            }
        }
    }

    public static ArrayList<UserGroup> selectAll() throws Exception {
        String sql = "SELECT * FROM [UserGroup] WHERE [Id] > 0 ORDER BY [category], [name]";
        try (Connection con = DB.getConnection();
             Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            ArrayList<UserGroup> list = new ArrayList<UserGroup>();
            while (rs.next()) list.add(makeUserGroup(rs, false));
            return list;
        }
    }

    public static ArrayList<UserGroup> selectAllWithCount(int order) throws Exception {
        String sql = "EXEC userGroupSelectAllWithCount ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, order);
            try (ResultSet rs = stmt.executeQuery()) {
                ArrayList<UserGroup> list = new ArrayList<UserGroup>();
                while (rs.next()) list.add(makeUserGroup(rs, true));
                return list;
            }
        }
    }

    public static void saveUserGroup(int userId, int[] userGroupIdList) throws Exception {
        String sql1 = "DELETE FROM [User_UserGroup] WHERE [userId] = ?";
        String sql2 = "INSERT [User_UserGroup] ([userId], [userGroupId]) VALUES (?, ?)";
        try (Connection con = DB.getConnection()) {
            con.setAutoCommit(false);
            try (PreparedStatement stmt1 = con.prepareStatement(sql1);
                 PreparedStatement stmt2 = con.prepareStatement(sql2)) {
                stmt1.setInt(1, userId);
                stmt1.executeUpdate();

                stmt2.setInt(1, userId);
                for (int userGroopId : userGroupIdList) {
                    stmt2.setInt(2, userGroopId);
                    stmt2.executeUpdate();
                }
                con.commit();
            } catch (Exception e) {
                con.rollback();
                throw e;
            }
            con.setAutoCommit(true);
        }
    }

    public static void update(int id, String name, String category) throws Exception {
        String sql = "UPDATE [UserGroup] SET [name] = ?, [category] = ? WHERE [id] = ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setString(1, name);
            stmt.setString(2, category);
            stmt.setInt(3, id);
            stmt.executeUpdate();
        }
    }

    public static void removeUser(int id, int[] userIdList) throws Exception {
        String sql = "DELETE FROM [User_UserGroup] WHERE [userGroupId] = ? AND [userId] = ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, id);
            for (int userId : userIdList) {
                stmt.setInt(2, userId);
                stmt.executeUpdate();
            }
        }
    }

    public static void addUser(int id, String[] loginIdList) throws Exception {
        String sql = "EXEC userGroupAddUser ?, ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, id);
            for (String loginId : loginIdList) {
                stmt.setString(2, loginId);
                stmt.executeUpdate();
            }
        }
    }

    public static void deleteUserGroup(int id) throws Exception {
        String sql = "EXEC userGroupDelete ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, id);
            stmt.executeUpdate();
        }
    }

    public static void insert(String title, String category) throws Exception {
        String sql = "INSERT INTO [UserGroup] ([name], [category]) VALUES (?,?)";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setString(1, title);
            stmt.setString(2, category);
            stmt.executeUpdate();
        }
    }
}
